package com.lecture.java8.excel.dyr.n.a004;

import com.alibaba.excel.EasyExcel;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelMerger2 {

    public static void main(String[] args) {
        String path = "D:\\study\\tmp\\1119\\";

        // 读取Excel1
        List<Excel1> excel1List = EasyExcel.read(path + "最新的合并表格2.xlsx").head(Excel1.class).sheet().doReadSync();

        // 读取Excel2
        List<Excel11> excel11List = EasyExcel.read(path + "11报刊财经新闻量化统计年度数据（1994-2023）.xls").head(Excel11.class).sheet().doReadSync();
        Map<String, Excel11> excel11Map = new HashMap<>();
        for (Excel11 excel11 : excel11List) {
            excel11Map.put(Integer.parseInt(excel11.getCode()) + "-" + excel11.getYear(), excel11);
        }

        List<Excel12> excel12List = EasyExcel.read(path + "12政府补助.xlsx").head(Excel12.class).sheet().doReadSync();
        Map<String, Excel12> excel12Map = new HashMap<>();
        for (Excel12 excel12 : excel12List) {
            excel12Map.put(Integer.parseInt(excel12.getCode()) + "-" + excel12.getYear(), excel12);
        }

        // 合并数据
        for (Excel1 excel1 : excel1List) {
            String key = Integer.parseInt(excel1.getCode()) + "-" + excel1.getYear();
            // 关联Excel11
            Excel11 excel11 = excel11Map.get(key);
            if (excel11 != null) {
                excel1.setBz(excel11.getBz());
                excel1.setCa(excel11.getCa());
            }

            Excel12 excel12 = excel12Map.get(key);
            if (excel12 != null) {
                excel1.setCb(excel12.getCb());
                excel1.setCc(excel12.getCc());
                excel1.setCe(excel12.getCe());
            }


        }

        // 导出新的Excel
        EasyExcel.write(path + "最新的合并表格3.xlsx", Excel1.class).sheet("Sheet1").doWrite(excel1List);
    }
}
